Role permissions disappear

I created a new database role to give a number of users select privilege only on some tables and every day I have to go back in and add the tables back to the role. Is there something I'm missing here?First, you don't add tables to roles...you grant permissions to users on objects.

Second, it sounds like you're either recreating the tables every night (unlikely), or youre doing a restore...

which is it...

There are no miracles...|||I think you hit the nail on the head. Most if not all of these tables are dropped and recreated every night. Doh!!! Sorry, I'm stumbling through this. Our DBA up and quit out of the blue and this got dumped in my lap.|||No sweat...set this up as a stored procedure, then schedule it as the last step of your nightly batch job..

just change PUBLIC to whatever role yo have...

DECLARE myGrants99 CURSOR FOR
SELECT 'GRANT SELECT ON [' + TABLE_NAME + '] TO PUBLIC '
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

OPEN myGrants99

DECLARE @.SQL varchar(8000)

FETCH NEXT FROM myGrants99 INTO @.SQL

WHILE @.@.FETCH_STATUS = 0
BEGIN
EXEC(@.SQL)
FETCH NEXT FROM myGrants99 INTO @.SQL
END

CLOSE myGrants99
DEALLOCATE myGrants99|||Thanks for this. It'll help tremendously. I'll give this to the guys that created the DTS packages to do the loads.|||OK, Now I have a silly question...

Why are they dropping the tables?

You'd be better off if they TRUNCATE the tables...

alos I'm assuming this is not an OTLP (going out on a limb, eh) database, ratyher a reporting/ warehouse...right?

Is there any RI?

Do you work in the Northeast US?

I work cheap...

:D|||Don't know why they're dropping and not truncating. I will suggest that to the developers. And this is a reporting warehouse for Crystal. Sorry, but working for a major telecommunications company doesn't allow me to contract outside assistance. OJT is our main source of training. Sounds really stupid doesn't it?|||Naaaahhhh

Jump in to the pool...deep end...feet first...keep your head above water...
Also, Go out and buy (and read) some good books...

Check out:

http://www.sqlteam.com/store.asp

EDIT: Oh, and keeping coming back to here or sql team...|||I'll definitely keep coming back. The response has been great. This is the second question I've posted here and both have been answered quickly.

Thanks again.

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
Prasad
Not all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>
|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>
|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Not...icationService]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
PraNot all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Pra
>|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[[url]http://schemas.microsoft.com/SQL/Notifications/EventNotificationService[
/url]]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Pra" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
Prasad
Not all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>
|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>
|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Not...icationService]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>
sql

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
PrasadNot all securables are objects. What class_desc have those objects in
sys.database_permissions?
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> w
rote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/No...Serv
ice]
TO [Public];
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>

Role Permissions

Hi,
I am trying the following on SQL Server 2005.
I want to find out the permissions for different roles on database and
database objects.
So, selected all the records from the "sys.database_permissions" catalog
view and joined it with the "sys.all_objects" catalog view.
But I found some objects that have the permissions assigned to them but
don't have an entry in sys.all_objects.
This behavior was for the "master" database.
The object ids for the objects is as follows :
-233346666
-282896470
-986524149
-369557355
The permission assigned is Select for all the above objects for public role.
I want to know what these objects are.
TIA
PrasadNot all securables are objects. What class_desc have those objects in
sys.database_permissions?
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
> Hi,
> I am trying the following on SQL Server 2005.
> I want to find out the permissions for different roles on database and
> database objects.
> So, selected all the records from the "sys.database_permissions" catalog
> view and joined it with the "sys.all_objects" catalog view.
> But I found some objects that have the permissions assigned to them but
> don't have an entry in sys.all_objects.
> This behavior was for the "master" database.
> The object ids for the objects is as follows :
> -233346666
> -282896470
> -986524149
> -369557355
> The permission assigned is Select for all the above objects for public
> role.
> I want to know what these objects are.
> TIA
> Prasad
>|||Those objects have the class_desc as "OBJECT_OR_COLUMN"
"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon> wrote
in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
> Not all securables are objects. What class_desc have those objects in
> sys.database_permissions?
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
> HTH,
> ~ Remus Rusanu
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>> Hi,
>> I am trying the following on SQL Server 2005.
>> I want to find out the permissions for different roles on database and
>> database objects.
>> So, selected all the records from the "sys.database_permissions" catalog
>> view and joined it with the "sys.all_objects" catalog view.
>> But I found some objects that have the permissions assigned to them but
>> don't have an entry in sys.all_objects.
>> This behavior was for the "master" database.
>> The object ids for the objects is as follows :
>> -233346666
>> -282896470
>> -986524149
>> -369557355
>> The permission assigned is Select for all the above objects for public
>> role.
>> I want to know what these objects are.
>> TIA
>> Prasad
>|||You can safely ignore these four objects, they shouldn't be there. You
should use http://lab.msdn.microsoft.com/productfeedback/ to report this
problem.
But in general your query will not work for securables that are not objects,
e.g. services. You can test this by running this:
GRANT SEND ON
SERVICE::[http://schemas.microsoft.com/SQL/Notifications/EventNotificationService]
TO [Public];
--
This posting is provided "AS IS" with no warranties, and confers no rights.
HTH,
~ Remus Rusanu
SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
"Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
news:eUPU8f%23GGHA.532@.TK2MSFTNGP15.phx.gbl...
> Those objects have the class_desc as "OBJECT_OR_COLUMN"
> "Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@.microsoft.com.nowhere.moon>
> wrote in message news:eibDbr0GGHA.1032@.TK2MSFTNGP11.phx.gbl...
>> Not all securables are objects. What class_desc have those objects in
>> sys.database_permissions?
>> --
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>> HTH,
>> ~ Remus Rusanu
>> SQL Service Broker
>> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>>
>> "Prasad" <ekke_nikhil@.yahoo.co.uk> wrote in message
>> news:%23Wg9iEyGGHA.1088@.tk2msftngp13.phx.gbl...
>> Hi,
>> I am trying the following on SQL Server 2005.
>> I want to find out the permissions for different roles on database and
>> database objects.
>> So, selected all the records from the "sys.database_permissions" catalog
>> view and joined it with the "sys.all_objects" catalog view.
>> But I found some objects that have the permissions assigned to them but
>> don't have an entry in sys.all_objects.
>> This behavior was for the "master" database.
>> The object ids for the objects is as follows :
>> -233346666
>> -282896470
>> -986524149
>> -369557355
>> The permission assigned is Select for all the above objects for public
>> role.
>> I want to know what these objects are.
>> TIA
>> Prasad
>>
>

Role

hi,
The issue for using a role is everytime if I create a new stored
procedure/view/table/udf, I need to go to the role and grant permissions for
that new role.
Am I right?
EdIf all the users in the role need to execute the stored procedure, then yes,
you will have to grant the EXECUTE previleges to the role. You can do this
with a simple GRANT statement like:
GRANT EXECUTE ON usp TO public
where usp is the procedure & public is the role.
Anith|||Thanks for your answer, that helps...
I am thinking in the Production Server, we don't want to grant Execute
permission to the uers, right?
"Anith Sen" wrote:

> If all the users in the role need to execute the stored procedure, then ye
s,
> you will have to grant the EXECUTE previleges to the role. You can do this
> with a simple GRANT statement like:
> GRANT EXECUTE ON usp TO public
> where usp is the procedure & public is the role.
> --
> Anith
>
>|||>> I am thinking in the Production Server, we don't want to grant Execute
I am not sure if I follow you. Stored procedures are meant to be executed by
database users. Without execute permission, regular database users cannot
run any procedures which they do not own.
Btw, I used public as an example in my previous post, all users belong to
public role by default.
Anithsql

Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!
You should have admin rights to start or stop services.
Regards
Amsh
sql

Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!You should have admin rights to start or stop services.
Regards
Amsh

Ristricted User Permissions on Services

Hi!
I have a user with restricted permissions on his account but still he
wants to start and stop SQL server services for MSDE (runtime SQL
server) installed on his computer. So whenever he tries to start or
stop the service he gets a message "Access Denied", so can anyone guide
me how can I enable atleast this feature on his machine still using his
restricted account.
cheers!!!You should have admin rights to start or stop services.
Regards
Amsh

Rights for Access2000 Upsizing-Wizard

I granted CREATE TABLE to the signed-in user, but the export fails. With sa
account it would work.
Which permissions are missing?
ThanksIf the database exists, the user will still need create
database permissions as well as permissions to create
tables, views, stored procedures, triggers, etc depending on
what objects you have in your Access database and what
options you select. If the database does not exist, the user
needs permissions to select from system tables in master.
Just granting Create table won't be enough.
-Sue
On Mon, 7 Mar 2005 05:59:04 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>I granted CREATE TABLE to the signed-in user, but the export fails. With sa
>account it would work.
>Which permissions are missing?
>Thanks|||Hi Sue
Thanks first for your help.
The database exists. I droped the user for recreation in master, added the
role db_owner and granted create database in master and new db.
Still don't work. Any idea? I import just tables, index and relations with
DRI.|||Go into master and execute:
grant create database to YourUser
Then go into the database that will be used for upsizing and
make the user the owner of the database by executing:
sp_changedbowner 'YourUser'
-Sue
On Tue, 8 Mar 2005 06:41:02 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>Hi Sue
>Thanks first for your help.
>The database exists. I droped the user for recreation in master, added the
>role db_owner and granted create database in master and new db.
>Still don't work. Any idea? I import just tables, index and relations with
>DRI.
>|||Hello Sue

> Go into master and execute:
> grant create database to YourUser
Done. What I do in a scratch database is:
use master
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
EXEC sp_addrolemember 'db_owner', 'usr'
grant CREATE DATABASE to usr
go
use myDB
EXEC sp_grantdbaccess 'usr'
exec sp_addrolemember 'db_owner', 'usr'
go

> Then go into the database that will be used for upsizing and
> make the user the owner of the database by executing:
> sp_changedbowner 'YourUser'
When I execute this statement, I get the error that it is already owner of
db. Maybe because of sp_addrolemember 'db_owner'?|||Remove the user from the database - you don't want the
account being a user in the database when assigning the
account as the owner of the database. Then execute
sp_changedbowner.
-Sue
On Tue, 8 Mar 2005 23:43:04 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>Hello Sue
>
>Done. What I do in a scratch database is:
> use master
> CREATE DATABASE myDB
> go
> EXEC sp_addlogin 'usr', 'pwd'
> EXEC sp_grantdbaccess 'usr'
> EXEC sp_addrolemember 'db_owner', 'usr'
> grant CREATE DATABASE to usr
> go
> use myDB
> EXEC sp_grantdbaccess 'usr'
> exec sp_addrolemember 'db_owner', 'usr'
> go
>
>When I execute this statement, I get the error that it is already owner of
>db. Maybe because of sp_addrolemember 'db_owner'?
>|||Sorry Sue
Don't understand anything. If I remove the user it can't be the owner
(because it doesn't exist). If I remove the dbaccess, I can't login anymore.
What do you mean?
Could you send me a script from scratch database like:
use master
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
EXEC sp_addrolemember 'db_owner', 'usr'
grant CREATE DATABASE to usr
go
use myDB
EXEC sp_grantdbaccess 'usr'
exec sp_addrolemember 'db_owner', 'usr'
go
Thanks|||This is the part you don't want to use. This adds the
account as a user in the database. You don't want the
account added as a user in the database. Don't execute this
part at all. Instead, make the user the database owner NOT a
member of the db_owner role.
So instead of this part, execute:
use myDB
exec sp_changedbowner 'usr'
There is a difference between being the database owner and
being a member of the db_owner role.
You script has another reference where you are adding the
usr to the db_owners role for master and you don't want
that.
Your entire script would read like:
use master
go
CREATE DATABASE myDB
go
EXEC sp_addlogin 'usr', 'pwd'
EXEC sp_grantdbaccess 'usr'
grant CREATE DATABASE to usr
go
use myDB
go
EXEC sp_changedbowner 'usr'
go
I just ran it and upsized a database logging in as usr and
using myDB as the destination database, upsizing tables,
indexes and DRI.
-Sue
On Fri, 11 Mar 2005 00:29:02 -0800, Bernhard Lauber
<BernhardLauber@.discussions.microsoft.com> wrote:

>use myDB
> EXEC sp_grantdbaccess 'usr'
> exec sp_addrolemember 'db_owner', 'usr'
> go|||Hi Sue
Thanks for you replies and patience. Unfortunately your script still doesn't
work. The tables in Access are skipped...|||Don't know what else to tell you - the permissions keep working for me just
fine. I just tried in another different environment (so we are up to three
now - different server, different PCs with Access DBs) and it worked fine.
I have upsized different databases 6 times now with a user with create
database permissions and the owner of the destination database for the
upsized objects. Upsized tables, indexes, DRI. Just followed the origninal
steps:
Go into master and execute:
grant create database to YourUser
Then go into the database that will be used for upsizing and
make the user the owner of the database by executing:
sp_changedbowner 'YourUser'
If the tables are skipped with no errors then basically nothing was upsized
as the indexes and DRI couldn't be done. You will need to track down where
the error is and provide an easily repro scenario as I am unable to reproduc
e
your problems.
-Sue

Revoking public permissions

I am doing some security work on SQL server and am concerned about the permi
sions on the public role. i want to strip down the permisions given to the
public role in the system databases. What are the implications of runnign sc
ripts to revoke all to publ
ic for all statements and objects in all databases. I have read this causes
problems? What have other folk done regarding this and is there a definitive
list anywhere?
Thanks in advanceGemmill,
We have many databases with all rights to user tables, views, stored
procedures, etc. revoked for public. (You can do this by simply never
granting any rights to public as well.) Also, make sure you do not have
the guest account. (I have never tried to revoke public rights to the
system tables, so I don't know if you can do that. I guess I can go try
when I get back to my office.)
The main implication is that you will break rights that your people have
become accustomed to.
Russell Fields
"Gemmill" <anonymous@.discussions.microsoft.com> wrote in message
news:2657C8D7-F49D-47D1-99B6-04D470BE6D71@.microsoft.com...
> I am doing some security work on SQL server and am concerned about the
permisions on the public role. i want to strip down the permisions given to
the public role in the system databases. What are the implications of
runnign scripts to revoke all to public for all statements and objects in
all databases. I have read this causes problems? What have other folk done
regarding this and is there a definitive list anywhere?
> Thanks in advance|||Thanks for the reply russell. Iam fairly comforatable with removing permissi
ons as susggested from user tables. But am concerned it will cause problems
if i carry it out on the system databases. I would like to "lock down" publi
c permissions on the system
databases without seriously affecting system operation. What have you done i
n regards to this.
Thanks|||My recommendation would be to leave the system tables alone. If there
are any security holes that turn up that arise from granting
permissions to public, then they'll probably be addressed in a service
pack, if they haven't been already. If you try mucking around in the
systems tables yourself, you'll probably just end up breaking things.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 27 Feb 2004 01:11:05 -0800, "gemmill"
<anonymous@.discussions.microsoft.com> wrote:

>Thanks for the reply russell. Iam fairly comforatable with removing permissions as
susggested from user tables. But am concerned it will cause problems if i carry it o
ut on the system databases. I would like to "lock down" public permissions on the sy
ste
m databases without seriously affecting system operation. What have you done in regards to
this.
>Thanks|||Best practices often tell you to remove PUBLIC role from all user objects an
d this should be done after a careful review of the permission required by y
our application.
Some experts also go as far as to recommend that you remove PUBLIC role perm
issions from system stored procedures and extended stored procedures to prev
ent attacks on your server. This should also be done, but only after carefu
l testing as removing PUBLI
C permissions to all stored procedures can often break some functionality in
Enterprise Manager. I do not know of a article/whitepaper that has studied
this and listed what exactly will be broken based on each system stored proc
edure.
Brian Kelley recently released a nice whitepaper on the PUBLIC role permissi
ons to system tables that you should read to see if it is any help.
SQL Server 2000: Permissions on System Tables Granted to Logins Due to the P
ublic Role
http://www.giac.org./practical/GSEC...Kelley_GSEC.pdf
Randy Dyess
www.Database-Security.Info|||Thanks for the reply's, much appreciated

Revoking permissions on system tables to the public role.

We have an audit issue that is requiring me to revoke the select permissions from the public role to the system tables. Has anyone had to do this?? What problems did you experience? Are their any tables that you were not able to change the permissions on? Any help is greatly appreciated...when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...

...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)

...as to what affect revoking system table access from the public role will have, you would first have to answer the above questions...

...also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?|||..when you say "system tables" which ones...system tables reside within databases and of course within master,model,msdb, etc. ...

--I mean all. From the master, msdb, and user databases. My concerns are mostly with the master and msdb databases.

...how are the users you want to restrict accessing the system tables?? (an application, queries, report writer, etc. ...)

--We use several types of applications. Everything from web pages( We are a web company), to PeopleSoft, Siebel, and Access.

also you state this is for auditing purposes, if you prove that you are unable to maintain functionality after revoking permissions are they going to allow an exception?
-- I am not sure, but I believe that they will. This is related to the new Sarbanes-Oxley act.|||...we are working on Sarbanes as well...but there has been nothing to compell us to look at this type of restriction...

...I am not sure you can restrict this access and still have application functionality...

...if this is still something you would like to pursue, I would start at the database level, create a test database and userid's and a method of access.
First you would have to test to ensure the access method works for the objects created in the database. Once that was proven, I would try the revoke against the databases system tables. To obtain a list of system tables:

select name from sysobjects where type = 'S'

you can then use the "sp_helprotect" stored procedure to obtain the current security settings.

then use the REVOKE command to revoke public access from these objects...

...then the access methid would have to be tested again...

now as far as the system databases goes, i'm sure that revoking public access would allow funtionality to remain...but you'd have to follow a similiar test...good luck...|||Why on earth would SOX be interested in the system tables? Are you storing SOX-sensitive data in the system tables? Using system tables for storing user data is a really bad idea!

-PatP|||...we discussed SOX at lunch and agree that everyone seems to be going overboard in the name of SOX...including auditors who are just guessing at how to comply with the new expectations...(not that they ever knew!)...

Revoking permissions from dbo

Hi,
Is there any possible way to revoke a permission in a database from someone
who is a database owner? I don't think there is, but I'm making a last ditch
attempt to find out (this is SQL Server 2005).
The reason is that we want to use the "VIEW ALL DATABASES" permission to
stop other users viewing databases that they don't own, but the only way to
do this is to make the user who is allowed to see the database the owner. Bu
t
by doing this, they get access to do things like backup databases etc which
is a security risk (this is a shared server).
All my research points to the fact that we can't stop them from backing up
databases etc if thye are the owner, and since we can't allow that, it means
"VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
permission would be especially useful for if it was implemented with care).
We really want to hide all databases from other users, but we can't do it if
it means opening large holes in our security.
If anyone has any ideas before I give up, they would be appreciated!I don't have 2005 on this laptop. I believe though that you can deny view
definition on all but the databases a particular person owns. They can stil
l
see theirs, but they won't be able to see others. Isn't that, in effect, th
e
exact thing you are trying to achieve?
--
MeanOldDBA
derrickleggett@.hotmail.com
http://weblogs.sqlteam.com/derrickl
When life gives you a lemon, fire the DBA.
"Paul Woodland" wrote:

> Hi,
> Is there any possible way to revoke a permission in a database from someon
e
> who is a database owner? I don't think there is, but I'm making a last dit
ch
> attempt to find out (this is SQL Server 2005).
> The reason is that we want to use the "VIEW ALL DATABASES" permission to
> stop other users viewing databases that they don't own, but the only way t
o
> do this is to make the user who is allowed to see the database the owner.
But
> by doing this, they get access to do things like backup databases etc whic
h
> is a security risk (this is a shared server).
> All my research points to the fact that we can't stop them from backing up
> databases etc if thye are the owner, and since we can't allow that, it mea
ns
> "VIEW ALL DATABASES" is useless for shared hosts (whom I would think the
> permission would be especially useful for if it was implemented with care)
.
> We really want to hide all databases from other users, but we can't do it
if
> it means opening large holes in our security.
> If anyone has any ideas before I give up, they would be appreciated!
>|||Hi,
Thank you for your reply. Although doing that hides the databases fine
(thats not the problem), it's the security holes it opens up which I'm
worried about. As an example, if we set our customers as database owners the
n
they can use "backup database" whenever they wish to backup to any location
on the servers hard disk that the user SQL Server is running as has access t
o
(which is a limited number of places because it's running as a limited user,
but there are still places - such as where we store our own automated
backups). Being a shared hosting environment rather than a corporate
environment, we can't "trust" our users not to take advantage of this (for
example, they could overwrite our own backups).
The way I can think of to solve this is to somehow revoke the "backup
database" permission (and other permissions you don't want them to have)
from the user, but it won't let you revoke permissions from dbo. Which is wh
y
I'm stuck.
Any ideas?
"MeanOldDBA" wrote:
[vbcol=seagreen]
> I don't have 2005 on this laptop. I believe though that you can deny view
> definition on all but the databases a particular person owns. They can st
ill
> see theirs, but they won't be able to see others. Isn't that, in effect,
the
> exact thing you are trying to achieve?
> --
> MeanOldDBA
> derrickleggett@.hotmail.com
> http://weblogs.sqlteam.com/derrickl
> When life gives you a lemon, fire the DBA.
>
> "Paul Woodland" wrote:
>

Revoking Permissions

I am going to tighten permissions on my SQL Server. In one book, it is reco
mmended to remove all permissions on the public role to user databases. By
default, it looks like the public role is granted permissions to the system
tables and system stored pr
ocedures.
Does the public role need access to these?
Thank you,
JLFlemingBooks Online has this recommendation:
To protect against unauthorized data access, minimize the permissions
granted to the public role. Instead, grant permissions to other database
roles and to user accounts associated with logins
There's some additional guidance here:
SQL Server 2000 SP3 Security Features and Best Practices: Security Best
Practices Checklist
http://www.microsoft.com/technet/pr...n/sp3sec04.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Revoking and Granting permissions.. SQL Security Bug\Issue ?

I am getting a very strange issue occurring when granting
and revoking permissions on Roles in my database.
As part of the testing that I'm doing in a Role I revoked
the EXEC permission on a stored procedure and when
attempting to run the function in my VB app which
referenced this procedure I got the error
message 'EXECUTE permission denied on stored
procedure...' which makes perfect sense. But when going
back and granting the permission back to the role when I
run the same function in my VB app I get a number
of 'SELECT permission denied on object.' errors.
And these SELECT statements are in reference to what the
stored procedure is running against ? Is this an issue
with SQL security ?
Can anybody provide any insight ?Hi,
I feel that some body have denied select permission on your user. This will
over rule the grant access given to the role assigned to your user. This can
be overcomed by granding select permission to your user, so as the deny will
be removed.
Thanks
Hari
MCDBA
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?|||Permissions on objects referenced by your stored procedure are not checked
as long as the following are true:
1) the objects have the same owner (unbroken ownership chain)
2) you are not referencing objects using dynamic SQL in your proc.
3) if the objects are in different databases and you are running SQL 2000
SP3, cross-database chaining needs to be enabled in the databases involved
and the object owners need to map to the same login (per #1 above)
You can read more about ownership chains in the SQL 2000 Books Online
<adminsql.chm::/ad_security_4iyb.htm>.
Hope this helps.
Dan Guzman
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:170c01c426ae$f400e1c0$a001280a@.phx.gbl...
> I am getting a very strange issue occurring when granting
> and revoking permissions on Roles in my database.
> As part of the testing that I'm doing in a Role I revoked
> the EXEC permission on a stored procedure and when
> attempting to run the function in my VB app which
> referenced this procedure I got the error
> message 'EXECUTE permission denied on stored
> procedure...' which makes perfect sense. But when going
> back and granting the permission back to the role when I
> run the same function in my VB app I get a number
> of 'SELECT permission denied on object.' errors.
> And these SELECT statements are in reference to what the
> stored procedure is running against ? Is this an issue
> with SQL security ?
> Can anybody provide any insight ?

Revoking All Permissions - Secure by Default

I am designing my software to be 'secure by default'.

I only grant permissions to entities that actually require them, to help prevent unexpected attacks like injection attacks. To do this, I have a SQL script that configures all the permissions on my database. It is basically a series of GRANT commands. Every time I modify a stored procedure or perform a similar action that causes the permissions on an object to be lost, I run the script and it reconfigures all the security correctly, on every object.

Over time, permissions have been granted that are not necessary, or no longer required. This may have happened for many reasons, development and testing not least.

At the top of my script, I want to REVOKE all permissions explicitly granted or denied to my user (for the purposes of this question, PUBLIC) so that I know that only permissions explicitly granted in the script will be given to the user.

There are too many objects in my database to maintain a list of "REVOKE ALL ON <object> TO PUBLIC" commands with any reliability. The statement "REVOKE ALL ON ALL TO PUBLIC" does not work either.

Is there a simple way to do this?

Maybe.|||

You can probably write a script to query the catalogs, find the permissions granted or denied to public, and issue a revoke statement for each of them. There isn't a simple statement to do this.

Another way would be to collect all the grants/denies made to public in your scripts and issue revokes for them.

Thanks
Laurentiu

|||

I almost expected that this would be the only answer. Thanks.

Scripts are acceptable, but not 'my style', if you get my meaning. I will probably write a stored procedure that I can call to do this for me - which will have to be deployed to each of my databases - unfortunately.

Thanks.

Revoke all permissions?

Is there a way to revoke all permissions? I want to setup a test database
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on the
security script being used on the test database?
Regards,
Jamie
Scott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:

> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on the
> security script being used on the test database?
> --
> Regards,
> Jamie

Revoke all permissions?

Is there a way to revoke all permissions? I want to setup a test database
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on t
he
security script being used on the test database?
--
Regards,
JamieScott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
--
set nocount on
go
if exists(select * from tempdb..sysobjects where id =
object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by
name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:

> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on
the
> security script being used on the test database?
> --
> Regards,
> Jamie

Revoke all permissions?

Is there a way to revoke all permissions? I want to setup a test database
with permissions that make more sense than the random ones that have been
placed on the database over the years (SQL 2000). There are too many
factors to know for sure the plan will cover all scenarios without a test.
Hoping... is there is a way to revoke all permissions and then re-add them
[via a script] so that the permission set is clean and based solely on the
security script being used on the test database?
--
Regards,
JamieScott Morris answered this one for me in another post with a script to check
for orphaned users.
Thanks Scott.
--
set nocount on
go
if exists(select * from tempdb..sysobjects where id =object_id('tempdb..#t_users'))
drop table #t_users
CREATE TABLE #t_users ( [name] sysname)
INSERT #t_users ( [name] )
SELECT [name] from sysusers where status = 2 and name <> 'dbo' order by name
declare @.lc_name sysname
SET @.lc_name = (SELECT MIN([name]) FROM #t_users)
WHILE @.lc_name IS NOT NULL
BEGIN
IF exists(select * FROM master..syslogins WHERE [name] = @.lc_name)
EXEC sp_change_users_login 'AUTO_FIX', @.lc_name
else
PRINT '*** not fixing ' + @.lc_name
SET @.lc_name = (SELECT MIN([name]) FROM #t_users WHERE [name] >
@.lc_name )
END
go
Regards,
Jamie
"thejamie" wrote:
> Is there a way to revoke all permissions? I want to setup a test database
> with permissions that make more sense than the random ones that have been
> placed on the database over the years (SQL 2000). There are too many
> factors to know for sure the plan will cover all scenarios without a test.
> Hoping... is there is a way to revoke all permissions and then re-add them
> [via a script] so that the permission set is clean and based solely on the
> security script being used on the test database?
> --
> Regards,
> Jamie