Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
keithnolan
Starting Member
5 Posts |
 Posted - 2007-04-10 : 10:40:37
|
| I have a DB creation script which starts with the following linesEXEC sp_addrole 'RAVENROLE'goIF NOT EXISTS (SELECT * FROM sys.database_principals WHERE name = N'USER1')CREATE USER [USER1] FOR LOGIN [USER1] WITH DEFAULT_SCHEMA=[USER1]goIF NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'USER1')EXEC sys.sp_executesql N'CREATE SCHEMA [USER1] AUTHORIZATION [USER1]'GOEXEC sp_addrolemember 'RAVENROLE', 'WS-NOLANK\TFSBUILD2'Basically what I am doing is creating a role, then adding user USER1 to it. The problem is USER1 has full update, insert etc. permissions on all tables in the database by default. Even when I deny that user permissions they can still update, insert etc.I am using Windows Authentication, User1 also exists as a windows user.How do I ensure that User1 has no permissions by default? I want to be able to explicitly add them. |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-10 : 10:45:20
|
| Did you put User1 in sysadmin role? Does that account in any windows group that has more permissions? |
 |
|
|
keithnolan
Starting Member
5 Posts |
Posted - 2007-04-10 : 17:01:38
|
| No it's not in Sysadmin role, the only role it's in is Public and the one I've created. It exists in a windows group but only has minimal access. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-04-10 : 17:06:26
|
| Is the windows acct a member of the local admin group?Have you checked what permissions the public role has?Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
keithnolan
Starting Member
5 Posts |
Posted - 2007-04-11 : 03:56:37
|
| I can't seem to find an permissions for the public role in the DB. In SQL Management Studio when I check under properties->securables the public role has no explicit permissions specified. Is there somewhere else that I should also be checking?The windows account is not a member of the local admin group. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-11 : 10:09:49
|
| Any domain group has sysadmin or dbo rights? Does the account belong to those groups? |
|
|
|
keithnolan
Starting Member
5 Posts |
Posted - 2007-04-11 : 10:49:51
|
| To eliminate the possibility of the account being part of any group. I have created a new local machine user and added them to the guests group in Windows to ensure they have minimal permissions. I've logged onto Windows as this user and explicitly denied them access to some tables through Management Studio. This new user can update/delete etc.I must be missing something fairly obvious somewhere. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-04-11 : 11:31:12
|
| You connect to sql with windows authentication as that windows user? Did you add sql login for that user? Which sql login you used to deny access for that user? |
|
|
|
keithnolan
Starting Member
5 Posts |
Posted - 2007-04-13 : 05:41:20
|
| Yes I am connecting using Windows Authentication as the that user. I have created an SQL login. I have created an SQL login for that user. I have denied them any permissions to some tables on a table by table basis yet the user can still perform operations on these tables. |
|
|
|
|
|
|
User has full DB access by default