Defining custom Roles with limited access to SQL Objects

Hi!

I'm assisting in the creation of a development enviroment with SQL Server 2005, and I need to assign some custom roles, in particular, a Stored Developer Role should be able to create, modify and execute Stored Procedures but they should not be able to alter tables or views, but should be able to retrieve/insert data from those tables.

I've tried with the default roles in 2005 to no avail.

Is there a relatively easy way to accomplish this with a database alredy populated with objects of both kinds? (SP's and Tables / Views)

Thanks!

You should create your own role and assign the required permissions to it. See CREATE ROLE for information on how to create a role.

Thanks
Laurentiu

Default SqlServer database creation path

Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.

Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS

declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT

SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]

|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||

The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

and

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

You can find the registry key for your SQL 2005 instance here:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

Now go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

to get SQLDataRoot and SQLPath...

then go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

to get DefaultData and DefaultLog...

Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

But fails for

master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',

N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT

it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths

Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1

Default SqlServer database creation path

Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.

Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS

declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT

SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]

|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||

The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

and

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

You can find the registry key for your SQL 2005 instance here:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

Now go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

to get SQLDataRoot and SQLPath...

then go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

to get DefaultData and DefaultLog...

Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

But fails for

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures', N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT

it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths

Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1

Default SqlServer database creation path

Hi,
How can i get default sql server(2005) database creation path using Tsql/ or system stored procedure? in previous(2000) i used undocumented sp(sp_MSget_setup_paths) to doing this.

Thanks
DishanHere you are, courtesy to SQL Profiler and SSMS

declare @.SmoDefaultFile nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

declare @.SmoDefaultLog nvarchar(512)
exec master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', @.SmoDefaultLog OUTPUT

SELECT ISNULL(@.SmoDefaultFile,N'') AS [DefaultFile], ISNULL(@.SmoDefaultLog,N'') AS [DefaultLog]

|||it return empty result set... im not set specific path for data/log when installing sql2005.|||If you have the default location for your database files, the script will not return anything.
Open SQL Server Management Studio, in the Object Explorer right click your instance name and select Properties. Select Database Settings page and change temporary the database default locations for data and log. And then run the script again.
To revert back use the same procedure and delete the path you've entered for the database default locations. That will remove the registry entries.|||

The "DefaultData" and "DefaultLog" only have data when you specify an alternate SQL Server Data or Log folder.

The undocumented SQL 2000 proc sp_MSget_setup_paths returned these registry entries:

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLDataRoot

and

HKLM\Software\Microsoft\MSSQLServer\Setup -- SQLPath

The app that I work on checks SQLDataRoot and SQLPath first, then replaces the path from SQLDataRoot with the DefaultData path if there is one, and replaces the derived Log path with DefaultLog if there is one...

If you are trying to read SQL 2005 registry entries for named instances, you will see that things are a little different. You'll need to derive the correct registry path from your instance name. Going to HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Name>\Setup will NOT work.

You can find the registry key for your SQL 2005 instance here:

HKLM\Software\Microsoft\Microsoft SQL Server\Instance Names\SQL - <Instance Name>

...the corresponding data is the registry key where you will find the paths for that instance...it will be something like MSSQL.number...let's call this <Instance Key>

Now go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\Setup

to get SQLDataRoot and SQLPath...

then go to:

HKLM\Software\Microsoft\Microsoft SQL Server\<Instance Key>\MSSQLServer

to get DefaultData and DefaultLog...

Perhaps this is too late to be of help to you, Dishan, but I had to solve a similar problem to support SQL 2005, so I hope this can help someone else out there.

|||Hi, your post has been useful in pointing me to the right direction but I cant get xp_instance_regread to return a result set where the registry entry has spaces in the key. So it works perfectly fine for example:

master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', @.SmoDefaultFile OUTPUT

But fails for

master.dbo.xp_instance_regread

N'HKEY_LOCAL_MACHINE',

N'Software\Microsoft\MSSQLServer\MSSQLServe\ExtendedProcedures',

N'Xp_regread&Allowed&Paths', @.SmoDefaultFile OUTPUT

it also fails if you try Xp_regread Allowed Paths
or Xp_regread%Allowed%Paths

Any ideas?
|||http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=965821&SiteID=1