I came across this code.
if not exists
(select * from dbo.sysusers where name = N'name' and uid < 16382) EXEC sp_grantdbaccess N'Name', N'Name'GO
i was wondering if anyone can tell me the significance of uid < 16382
thanks
Create user
Hot Topic
- SSIS Send Mail task problem
- Cannot create a report - VS 2005/SQL 2005
- report header: how do i have underline
- Calling SSIS packages from within a Script task
- Help SVP: How to connect to external server and backup db with SSMSExpress
- Create Temp Table Help
- See the error log for more details.
- Do SQL Server 2005/CLR Triggers support .config Files?
- Exception of type 'System.OutOfMemoryException' was thrown. (Microsoft.SqlServer.SmoEnum)
- Cannot create a connection to data source !!!! SSRS 2005
SQL Server
- Merge replication with anonymous subscribers and identity columns
- Transactional Replication sp question
- Recordcount logging
- Sql Server internal error
- Export Query Results To Excel
- Error: Failed to generate a user instance...
- Data Access very slow in .net as compared to VB
- Gettint error "product level is insufficient" - did full re-install of SQL 2005 Standard Edition
- J# newbie having SQL trouble in windows form
- data flow,
Create user
Mark Phelps
Run the query:
select * from dbo.sysusers --in 2000 and earlier
The database roles will start at 16383, users are less than this.
This isn't the case in 2005 (it is all I have to test on now, and it puts them all together and you can see them as different based on metadata.)
Louis
Bailey58642
Thanks.
I ran the query on both 2000 and 2005.
Database roles in 2005 start at 16383 as well.
Wilson Lim
The fixed roles are the ones that start at 16383, but user ones are mixed in. I think this is some ancient holdover because of how 6.5 did it, or maybe 7.0.
Either way, in 2000 and 2005, they have columns in sysusers to tell you if it is a role or a user:
--2000
select name, uid
from sysusers where issqlrole = 0
--2005
select name, principal_id
from sys.database_principals
where type_desc = 'database_role'
Or, using a compatibilty view:
select name, uid
from sys.sysusers where issqlrole = 0
The < 16382 thing would eliminate fixed database roles, but not reliably if you have a really large number of users. In 2005, they have a column in the database_principals view of is_fixed_role:
select name, principal_id
from sys.database_principals
where type_desc = 'database_role'
and is_fixed_role = 0
Jackey Cheung