May 30, 2008 at 7:59 am
We recently upgraded a SQL Server 2000 SP4 to SQL Server 2005 SP2 Post GDR.
Prior to the upgrade, there were no issues with creating SQL or Windows Logins. However, post the upgrade on adding a login (SQL or Windows) it doesn't show up in the following query.
SELECT
[sysusers].[uid],
[master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
It will show up only when you explicitly provide it access to any object in the master database. Has anyone seen this happen before? Any suggestions?
TIA
May 30, 2008 at 8:16 am
S A (5/30/2008)
We recently upgraded a SQL Server 2000 SP4 to SQL Server 2005 SP2 Post GDR.Prior to the upgrade, there were no issues with creating SQL or Windows Logins. However, post the upgrade on adding a login (SQL or Windows) it doesn't show up in the following query.
SELECT
[sysusers].[uid],
[master].[dbo].[syslogins].[loginname]
FROM [dbo].[sysusers]
INNER JOIN [master].[dbo].[syslogins]
ON [sysusers].[sid] = [master].[dbo].[syslogins].[sid]
It will show up only when you explicitly provide it access to any object in the master database. Has anyone seen this happen before? Any suggestions?
TIA
Check the all DB's Compatibility level If it is SQL server 2000(80) then you need to change it to SQL server 2005 (90). try it and now you could be able to.
MCP, MCTS (GDBA/EDA)
May 30, 2008 at 9:42 am
I changed the compatibility level (master database) to 90 but still the same issue. It won't add the new user to the sysusers table in the master database while creating the login.
May 30, 2008 at 9:47 am
Are you sure it's not just the inner join that is tripping you up?
I would expect that query to not show any users that don't have a login to the master database.
The Redneck DBA
May 30, 2008 at 9:55 am
Try to use from GUI (SSMS or EM). and check for the error log if happeen.
MCP, MCTS (GDBA/EDA)
May 30, 2008 at 10:04 am
i added 'dom\group' and tried executing
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'dom\group')
PRINT '1'
PRINT '2'
and as expected it Prints '2'
CREATE PROC sp_test
AS
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'dom\group')
PRINT '1'
PRINT '2'
GO
This print '2' as well
However
ALTER PROC sp_test
WITH EXECUTE AS OWNER
AS
IF NOT EXISTS (SELECT name FROM sys.server_principals WHERE name = 'dom\group')
PRINT '1'
PRINT '2'
GO
prints '1' and '2'
The same code "WITH EXECUTE AS OWNER" works on other machines (fresh install) but doesn't on this one. I checked the owner of the db am creating it and it is 'sa'
May 30, 2008 at 11:37 am
You can use the following script but before I want you to make sure,"Do you have the SA permission on the server or not?" If not than you should not be able to do it.
Syntax
sp_addlogin [ @loginame = ] 'login'
[ , [ @passwd = ] 'password' ]
[ , [ @defdb = ] 'database' ]
[ , [ @deflanguage = ] 'language' ]
[ , [ @sid = ] sid ]
[ , [ @encryptopt = ] 'encryption_option' ]
Stored procedure Description
sp_grantlogin Adds a Windows NT user or group.
sp_password Changes a user's password.
sp_defaultdb Changes a user's default database.
sp_defaultlanguage Changes a user's default language.
If the name of a default database is supplied, you can connect to the specified database without executing the USE statement. However, you cannot use the default database until given access to that database by the database owner (using sp_adduser or sp_addrolemember) or sp_addrole.
MCP, MCTS (GDBA/EDA)
May 30, 2008 at 11:54 am
The crux of the problem seems is the "WITH EXECUTE AS CALLER" statement. There are several procedures with the same t-sql having the same issue.
Anyone seen this problem before. This code works on fresh installs of SQL Server.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply