DSO in SQL2005: login

Showing posts with label login. Show all posts

Tuesday, March 27, 2012

Dropping Data Connect String

I've created a data source connection using the sa login and password with
the save password option. Running the stored procedure returns a good result
set. Ok, I'm feeling pretty good at this point . But after clicking on the
Preview tab, I receive the following message: "A connection cannot be made
to the database. Set and test the connection string. Login failed for
DIM\tj."
Of course testing the connection works fine, but why is trying to
authenticate using my credentials when it should be use the connect string?
I'm running RS SP1 against a SQL 2000 db.
--
Any and all contributions are greatly appreciated ...
Regards TJIt couldn't be something inside the stored procedure, could it? Can you do a
straight select?
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"TJ" <nospam@.nowhere.com> wrote in message
news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> I've created a data source connection using the sa login and password with
> the save password option. Running the stored procedure returns a good
> result
> set. Ok, I'm feeling pretty good at this point . But after clicking on the
> Preview tab, I receive the following message: "A connection cannot be made
> to the database. Set and test the connection string. Login failed for
> DIM\tj."
> Of course testing the connection works fine, but why is trying to
> authenticate using my credentials when it should be use the connect
> string?
> I'm running RS SP1 against a SQL 2000 db.
> --
> Any and all contributions are greatly appreciated ...
> Regards TJ
>|||If it is, I'm not seeing any errors when I execute the stored procedure on
the Data Tab. Is there an error log for the Data Tab?
"Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> It couldn't be something inside the stored procedure, could it? Can you do
a
> straight select?
> --
> Brian Welcker
> Group Program Manager
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "TJ" <nospam@.nowhere.com> wrote in message
> news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > I've created a data source connection using the sa login and password
with
> > the save password option. Running the stored procedure returns a good
> > result
> > set. Ok, I'm feeling pretty good at this point . But after clicking on
the
> > Preview tab, I receive the following message: "A connection cannot be
made
> > to the database. Set and test the connection string. Login failed for
> > DIM\tj."
> >
> > Of course testing the connection works fine, but why is trying to
> > authenticate using my credentials when it should be use the connect
> > string?
> >
> > I'm running RS SP1 against a SQL 2000 db.
> > --
> > Any and all contributions are greatly appreciated ...
> > Regards TJ
> >
> >
>|||I am running into the exact same issue... Any resolution to this yet?
"TJ" wrote:
> If it is, I'm not seeing any errors when I execute the stored procedure on
> the Data Tab. Is there an error log for the Data Tab?
> "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > It couldn't be something inside the stored procedure, could it? Can you do
> a
> > straight select?
> >
> > --
> > Brian Welcker
> > Group Program Manager
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> >
> > "TJ" <nospam@.nowhere.com> wrote in message
> > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > I've created a data source connection using the sa login and password
> with
> > > the save password option. Running the stored procedure returns a good
> > > result
> > > set. Ok, I'm feeling pretty good at this point . But after clicking on
> the
> > > Preview tab, I receive the following message: "A connection cannot be
> made
> > > to the database. Set and test the connection string. Login failed for
> > > DIM\tj."
> > >
> > > Of course testing the connection works fine, but why is trying to
> > > authenticate using my credentials when it should be use the connect
> > > string?
> > >
> > > I'm running RS SP1 against a SQL 2000 db.
> > > --
> > > Any and all contributions are greatly appreciated ...
> > > Regards TJ
> > >
> > >
> >
> >
>
>|||Here are a couple of things you might try:
a) I was calling a stored procedure from within a stored procedure using the
exec command; The report data connection account didn't have permissions for
the stored procedure inside the main stored procedure. I found the
permissions issue by running the main stored procedure in Query Analyzer,
but you need to open your Query Analyzer connection using the same data
connection information being used in your report.
b) You can hard code the User Id and password setting in the Connection
String on the Data Source Tab for the report.
Good Luck
TJ
"StanDaMon" <StanDaMon@.discussions.microsoft.com> wrote in message
news:102E7428-08C9-467E-8DC6-B69A4E8D094E@.microsoft.com...
> I am running into the exact same issue... Any resolution to this yet?
> "TJ" wrote:
> > If it is, I'm not seeing any errors when I execute the stored procedure
on
> > the Data Tab. Is there an error log for the Data Tab?
> >
> > "Brian Welcker [MSFT]" <bwelcker@.online.microsoft.com> wrote in message
> > news:ek08dp7pEHA.592@.TK2MSFTNGP11.phx.gbl...
> > > It couldn't be something inside the stored procedure, could it? Can
you do
> > a
> > > straight select?
> > >
> > > --
> > > Brian Welcker
> > > Group Program Manager
> > > SQL Server Reporting Services
> > >
> > > This posting is provided "AS IS" with no warranties, and confers no
> > rights.
> > >
> > > "TJ" <nospam@.nowhere.com> wrote in message
> > > news:Oi3Y$gzpEHA.556@.tk2msftngp13.phx.gbl...
> > > > I've created a data source connection using the sa login and
password
> > with
> > > > the save password option. Running the stored procedure returns a
good
> > > > result
> > > > set. Ok, I'm feeling pretty good at this point . But after clicking
on
> > the
> > > > Preview tab, I receive the following message: "A connection cannot
be
> > made
> > > > to the database. Set and test the connection string. Login failed
for
> > > > DIM\tj."
> > > >
> > > > Of course testing the connection works fine, but why is trying to
> > > > authenticate using my credentials when it should be use the connect
> > > > string?
> > > >
> > > > I'm running RS SP1 against a SQL 2000 db.
> > > > --
> > > > Any and all contributions are greatly appreciated ...
> > > > Regards TJ
> > > >
> > > >
> > >
> > >
> >
> >
> >

Thursday, March 22, 2012

Drop User

When I attempt to drop a user I receive an error that the user objects
objects in the SQL 2000 DB, and the login could not be dropped. Does anyone
have a workaround to this? How can you get around this. Perhaps changing
ownership globally, and then deleteing the login? Any Ideas?Hi,
You can not drop the user if the user owns any objects.
How to change the object owner:
sp_changeobjectowner 'obj_name','new_owner'
You can also change the owner by updating the sysobjects tables
update sysobjects
set uid=<new uid>
where uid='uid for the user you need to drop'
Thanks
Hari
MCDBA
"Casey" <casey.canales@.bestsoftware.com> wrote in message
news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> When I attempt to drop a user I receive an error that the user objects
> objects in the SQL 2000 DB, and the login could not be dropped. Does
anyone
> have a workaround to this? How can you get around this. Perhaps changing
> ownership globally, and then deleteing the login? Any Ideas?
>|||> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
Hari, although this may work, Casey should probably use the supported method
(sp_changedbowner).
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Hi,
> You can not drop the user if the user owns any objects.
> How to change the object owner:
> sp_changeobjectowner 'obj_name','new_owner'
> You can also change the owner by updating the sysobjects tables
> update sysobjects
> set uid=<new uid>
> where uid='uid for the user you need to drop'
> Thanks
> Hari
> MCDBA
>
> "Casey" <casey.canales@.bestsoftware.com> wrote in message
> news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > When I attempt to drop a user I receive an error that the user objects
> > objects in the SQL 2000 DB, and the login could not be dropped. Does
> anyone
> > have a workaround to this? How can you get around this. Perhaps
changing
> > ownership globally, and then deleteing the login? Any Ideas?
> >
> >
>|||Hi,
I agree with Dan. I just mentioned various possibilities to change the
object owner.
Casey,
Please use sp_changeobjectowner system stored procedure to change the object
owners. This is always safe.
Updating system tables is always risky.
Thanks
Hari
MCDBA
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>|||Oops, I meant sp_changeobjectowner.
--
Dan Guzman
SQL Server MVP
"Dan Guzman" <danguzman@.nospam-earthlink.net> wrote in message
news:OiJAsV1IEHA.3840@.TK2MSFTNGP11.phx.gbl...
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> Hari, although this may work, Casey should probably use the supported
method
> (sp_changedbowner).
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:ePdLZpwIEHA.3556@.TK2MSFTNGP10.phx.gbl...
> > Hi,
> >
> > You can not drop the user if the user owns any objects.
> >
> > How to change the object owner:
> >
> > sp_changeobjectowner 'obj_name','new_owner'
> >
> > You can also change the owner by updating the sysobjects tables
> >
> > update sysobjects
> > set uid=<new uid>
> > where uid='uid for the user you need to drop'
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> > "Casey" <casey.canales@.bestsoftware.com> wrote in message
> > news:u0nvimwIEHA.2688@.tk2msftngp13.phx.gbl...
> > > When I attempt to drop a user I receive an error that the user objects
> > > objects in the SQL 2000 DB, and the login could not be dropped. Does
> > anyone
> > > have a workaround to this? How can you get around this. Perhaps
> changing
> > > ownership globally, and then deleteing the login? Any Ideas?
> > >
> > >
> >
> >
>

Monday, March 19, 2012

Drop role, user, login

Okay I figured out how to determine if stored procs and funcs exist before dropping them.

How do I do the same for ROLE, LOGIN, USER?

I want get rid of annoying messages in my scripts when trying to drop something that doesn't exist.

Server 2005 and Server Express 2005

Thanks

For logins you can query sys.server_principals, for users you can query sys.database_principals or USER_ID() and for roles sys.database_principals. Ex:

-- logins (if you want to drop certificate based logins then you need to check for other types)

if exists(select * from sys.server_principals

where type IN ('S', 'U', 'G') and name = @.name)

begin

set @.name = quotename('somelogin')

exec('drop login ' + @.name)

end

-- users

if USER_ID(@.name) is not null

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- users

if exists(select * from sys.database_principals

where type IN ('S') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop user ' + @.name)

end

-- roles

if exists(select * from sys.database_principals

where type IN ('R') and name = @.name)

begin

set @.name = quotename('someuser')

exec('drop role ' + @.name)

end

See the BOL "security catalog views" topic for more details.

|||Many thanks

drop login Stored Procedure

hi ,
I would like to drop all invalid logins in my 2000 and 2005 environment . Do
we a SP that runs on both 2000 and 2005 to drop the logins .
Thanks & Regards
SM
sp_droplogin?
"moharil" <sid_m15@.yahoo.com> wrote in message
news:2EAA8272-02A0-43E6-ABA0-042B5F0E355B@.microsoft.com...
> hi ,
> I would like to drop all invalid logins in my 2000 and 2005 environment .
> Do
> we a SP that runs on both 2000 and 2005 to drop the logins .
> --
> Thanks & Regards
> SM
|||Hi
"moharil" wrote:

> hi ,
> I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> we a SP that runs on both 2000 and 2005 to drop the logins .
> --
> Thanks & Regards
> SM
sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
John
|||Thanks but I knew drop login . sorry I should had framed it in another manner
.. I am looking for a generalized script/sp that will run on all sql server
and drop the invalid login and send us a mail saying the login has been
dropped.
Thanks & Regards
Sid
"John Bell" wrote:

> Hi
> "moharil" wrote:
>
> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
> John
|||i have 2 sybase sp' s that run using the below listed SP i need something
similar that runs on sql server 2000-05
******************sp_drop_login_completely ****************
CREATE PROC sp_drop_login_completely @.login varchar(30)
as
declare @.msg varchar(20),
@.cnt int,
@.ret_code int,
@.db varchar(30),
@.status smallint,
@.proc_name varchar(92),
@.grp_nm varchar(30),
@.aliased_user_nm varchar(30)
select @.status = 0
select @.ret_code = 0
create table #display (db_nm varchar(30), grp_nm varchar(30) null,
aliased_user_nm varchar(30) null)
/*
** Delete user/alias from databases for this login
*/
declare databases_crs cursor for
select name, status=status & 1024 from master..sysdatabases
where
status & 1024 != 1024 /* not read only */
and status & 256 != 256 /* not suspect */
and status & 44 != 44 /* not in for load status */
for read only
open databases_crs
fetch databases_crs into @.db, @.status
WHILE (@.@.sqlstatus = 0)
BEGIN
select @.grp_nm = null, @.aliased_user_nm = null
select @.proc_name = @.db + "..sp_phh_model_group_nm"
exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
output
IF @.grp_nm is not null
BEGIN
insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
select @.proc_name = @.db + "..sp_dropuser"
exec @.ret_code = @.proc_name @.login
if @.ret_code != 0
print 'Error: Unable to drop Sybase user %1!, on database %2! ',
@.login, @.db
END
IF @.aliased_user_nm is not null
BEGIN
insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
select @.proc_name = @.db + "..sp_dropalias"
exec @.ret_code = @.proc_name @.login,"force"
if (@.ret_code != 0)
print 'Error: Unable to drop Sybase alias %1!, on database %2!
', @.login, @.db
END
select @.status = 0
fetch databases_crs into @.db, @.status
select @.status = @.status
END
close databases_crs
deallocate cursor databases_crs
/*
** Now that @.login isn't in any databases, drop the login
*/
if suser_id(@.login) is not null
BEGIN
exec sp_droplogin @.login
if (@.@.error != 0)
print 'Error: Unable to drop Sybase login %1!', @.login
END
/*
** Show where the user was
*/
print 'Login: %1! was removed from the following databases', @.login
select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
isnull(aliased_user_nm,'') as aliased_user_nm from #display
return
go
*************sp_phh_model_group_nm**************** ***
create proc sp_model_group_nm
@.model_to_follow varchar(30),
@.grp_nm_model_is_in varchar(30) output,
@.aliased_user_nm varchar(30) output
as
select @.grp_nm_model_is_in = g.name
from sysusers u, sysusers g,
master.dbo.syslogins m
where u.suid *= m.suid
and u.gid *= g.uid
and u.name = @.model_to_follow
and u.uid <= 16383 and u.uid != 0
select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid =
b.suid)
from sysalternates a
where suser_name(a.suid) = @.model_to_follow
return
go
*******************************
Thanks & Regards
Sid
"John Bell" wrote:

> Hi
> "moharil" wrote:
>
> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
> John
|||Hi
Run this query to identify ophaned logins and then create a script
(cursor with sp_droplogin ) to delete them
select sl.name
from master..syslogins sl
join sysusers su on sl.sid<>sl.sid
"moharil" <sid_m15@.yahoo.com> wrote in message
news:6E568992-1A2F-49BE-B2C3-BB9A532D57C2@.microsoft.com...[vbcol=seagreen]
> Thanks but I knew drop login . sorry I should had framed it in another
> manner
> . I am looking for a generalized script/sp that will run on all sql server
> and drop the invalid login and send us a mail saying the login has been
> dropped.
> --
> Thanks & Regards
> Sid
>
> "John Bell" wrote:
|||Uri
"Uri Dimant" wrote:

> Hi
> Run this query to identify ophaned logins and then create a script
> (cursor with sp_droplogin ) to delete them
> select sl.name
> from master..syslogins sl
> join sysusers su on sl.sid<>sl.sid
>
This doesn't make sense
John
|||can we modify the listed sybase SP's to run on sql servers ?
Thanks & Regards
Sid
"John Bell" wrote:

> Uri
> "Uri Dimant" wrote:
>
> This doesn't make sense
> John
>
|||Hi
"moharil" wrote:

> i have 2 sybase sp' s that run using the below listed SP i need something
> similar that runs on sql server 2000-05
> ******************sp_drop_login_completely ****************
> CREATE PROC sp_drop_login_completely @.login varchar(30)
> as
> declare @.msg varchar(20),
> @.cnt int,
> @.ret_code int,
> @.db varchar(30),
> @.status smallint,
> @.proc_name varchar(92),
> @.grp_nm varchar(30),
> @.aliased_user_nm varchar(30)
> select @.status = 0
> select @.ret_code = 0
> create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> aliased_user_nm varchar(30) null)
> /*
> ** Delete user/alias from databases for this login
> */
> declare databases_crs cursor for
> select name, status=status & 1024 from master..sysdatabases
> where
> status & 1024 != 1024 /* not read only */
> and status & 256 != 256 /* not suspect */
> and status & 44 != 44 /* not in for load status */
> for read only
> open databases_crs
> fetch databases_crs into @.db, @.status
> WHILE (@.@.sqlstatus = 0)
> BEGIN
> select @.grp_nm = null, @.aliased_user_nm = null
> select @.proc_name = @.db + "..sp_phh_model_group_nm"
> exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> output
> IF @.grp_nm is not null
> BEGIN
> insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> select @.proc_name = @.db + "..sp_dropuser"
> exec @.ret_code = @.proc_name @.login
> if @.ret_code != 0
> print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> @.login, @.db
> END
> IF @.aliased_user_nm is not null
> BEGIN
> insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> select @.proc_name = @.db + "..sp_dropalias"
> exec @.ret_code = @.proc_name @.login,"force"
> if (@.ret_code != 0)
> print 'Error: Unable to drop Sybase alias %1!, on database %2!
> ', @.login, @.db
> END
> select @.status = 0
> fetch databases_crs into @.db, @.status
> select @.status = @.status
> END
> close databases_crs
> deallocate cursor databases_crs
> /*
> ** Now that @.login isn't in any databases, drop the login
> */
> if suser_id(@.login) is not null
> BEGIN
> exec sp_droplogin @.login
> if (@.@.error != 0)
> print 'Error: Unable to drop Sybase login %1!', @.login
> END
> /*
> ** Show where the user was
> */
> print 'Login: %1! was removed from the following databases', @.login
> select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> isnull(aliased_user_nm,'') as aliased_user_nm from #display
> return
> go
> *************sp_phh_model_group_nm**************** ***
> create proc sp_model_group_nm
> @.model_to_follow varchar(30),
> @.grp_nm_model_is_in varchar(30) output,
> @.aliased_user_nm varchar(30) output
> as
> select @.grp_nm_model_is_in = g.name
> from sysusers u, sysusers g,
> master.dbo.syslogins m
> where u.suid *= m.suid
> and u.gid *= g.uid
> and u.name = @.model_to_follow
> and u.uid <= 16383 and u.uid != 0
> select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid =
> b.suid)
> from sysalternates a
> where suser_name(a.suid) = @.model_to_follow
> return
> go
> *******************************
> --
> Thanks & Regards
> Sid
Look at using the DATABASE_PROPERTY function instead of checking the status,
and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
sysusers to determine whether the user is a role or not.
There is an assumption that the users associated to the login do not own any
objects, check sysobjects and information_schema.schemata would be able to
deterine these.
John
|||Hi John

> This doesn't make sense
What did you mean?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
> Uri
> "Uri Dimant" wrote:
>
> This doesn't make sense
> John
>

drop login Stored Procedure

hi ,
I would like to drop all invalid logins in my 2000 and 2005 environment . Do
we a SP that runs on both 2000 and 2005 to drop the logins .
--
Thanks & Regards
SMsp_droplogin?
"moharil" <sid_m15@.yahoo.com> wrote in message
news:2EAA8272-02A0-43E6-ABA0-042B5F0E355B@.microsoft.com...
> hi ,
> I would like to drop all invalid logins in my 2000 and 2005 environment .
> Do
> we a SP that runs on both 2000 and 2005 to drop the logins .
> --
> Thanks & Regards
> SM|||Hi
"moharil" wrote:
> hi ,
> I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> we a SP that runs on both 2000 and 2005 to drop the logins .
> --
> Thanks & Regards
> SM
sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
John|||Thanks but I knew drop login . sorry I should had framed it in another manner
. I am looking for a generalized script/sp that will run on all sql server
and drop the invalid login and send us a mail saying the login has been
dropped.
--
Thanks & Regards
Sid
"John Bell" wrote:
> Hi
> "moharil" wrote:
> > hi ,
> > I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> > we a SP that runs on both 2000 and 2005 to drop the logins .
> > --
> > Thanks & Regards
> > SM
> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
> John|||i have 2 sybase sp' s that run using the below listed SP i need something
similar that runs on sql server 2000-05
******************sp_drop_login_completely ****************
CREATE PROC sp_drop_login_completely @.login varchar(30)
as
declare @.msg varchar(20),
@.cnt int,
@.ret_code int,
@.db varchar(30),
@.status smallint,
@.proc_name varchar(92),
@.grp_nm varchar(30),
@.aliased_user_nm varchar(30)
select @.status = 0
select @.ret_code = 0
create table #display (db_nm varchar(30), grp_nm varchar(30) null,
aliased_user_nm varchar(30) null)
/*
** Delete user/alias from databases for this login
*/
declare databases_crs cursor for
select name, status=status & 1024 from master..sysdatabases
where
status & 1024 != 1024 /* not read only */
and status & 256 != 256 /* not suspect */
and status & 44 != 44 /* not in for load status */
for read only
open databases_crs
fetch databases_crs into @.db, @.status
WHILE (@.@.sqlstatus = 0)
BEGIN
select @.grp_nm = null, @.aliased_user_nm = null
select @.proc_name = @.db + "..sp_phh_model_group_nm"
exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
output
IF @.grp_nm is not null
BEGIN
insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
select @.proc_name = @.db + "..sp_dropuser"
exec @.ret_code = @.proc_name @.login
if @.ret_code != 0
print 'Error: Unable to drop Sybase user %1!, on database %2! ',
@.login, @.db
END
IF @.aliased_user_nm is not null
BEGIN
insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
select @.proc_name = @.db + "..sp_dropalias"
exec @.ret_code = @.proc_name @.login,"force"
if (@.ret_code != 0)
print 'Error: Unable to drop Sybase alias %1!, on database %2!
', @.login, @.db
END
select @.status = 0
fetch databases_crs into @.db, @.status
select @.status = @.status
END
close databases_crs
deallocate cursor databases_crs
/*
** Now that @.login isn't in any databases, drop the login
*/
if suser_id(@.login) is not null
BEGIN
exec sp_droplogin @.login
if (@.@.error != 0)
print 'Error: Unable to drop Sybase login %1!', @.login
END
/*
** Show where the user was
*/
print 'Login: %1! was removed from the following databases', @.login
select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
isnull(aliased_user_nm,'') as aliased_user_nm from #display
return
go
*************sp_phh_model_group_nm*******************
create proc sp_model_group_nm
@.model_to_follow varchar(30),
@.grp_nm_model_is_in varchar(30) output,
@.aliased_user_nm varchar(30) output
as
select @.grp_nm_model_is_in = g.name
from sysusers u, sysusers g,
master.dbo.syslogins m
where u.suid *= m.suid
and u.gid *= g.uid
and u.name = @.model_to_follow
and u.uid <= 16383 and u.uid != 0
select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid =b.suid)
from sysalternates a
where suser_name(a.suid) = @.model_to_follow
return
go
*******************************
--
Thanks & Regards
Sid
"John Bell" wrote:
> Hi
> "moharil" wrote:
> > hi ,
> > I would like to drop all invalid logins in my 2000 and 2005 environment . Do
> > we a SP that runs on both 2000 and 2005 to drop the logins .
> > --
> > Thanks & Regards
> > SM
> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
> John|||Hi
Run this query to identify ophaned logins and then create a script
(cursor with sp_droplogin ) to delete them
select sl.name
from master..syslogins sl
join sysusers su on sl.sid<>sl.sid
"moharil" <sid_m15@.yahoo.com> wrote in message
news:6E568992-1A2F-49BE-B2C3-BB9A532D57C2@.microsoft.com...
> Thanks but I knew drop login . sorry I should had framed it in another
> manner
> . I am looking for a generalized script/sp that will run on all sql server
> and drop the invalid login and send us a mail saying the login has been
> dropped.
> --
> Thanks & Regards
> Sid
>
> "John Bell" wrote:
>> Hi
>> "moharil" wrote:
>> > hi ,
>> > I would like to drop all invalid logins in my 2000 and 2005 environment
>> > . Do
>> > we a SP that runs on both 2000 and 2005 to drop the logins .
>> > --
>> > Thanks & Regards
>> > SM
>> sp_dropLogin will work on SQL 2005 although DROP LOGIN is preferred.
>> John|||Uri
"Uri Dimant" wrote:
> Hi
> Run this query to identify ophaned logins and then create a script
> (cursor with sp_droplogin ) to delete them
> select sl.name
> from master..syslogins sl
> join sysusers su on sl.sid<>sl.sid
>
This doesn't make sense
John|||can we modify the listed sybase SP's to run on sql servers ?
--
Thanks & Regards
Sid
"John Bell" wrote:
> Uri
> "Uri Dimant" wrote:
> > Hi
> > Run this query to identify ophaned logins and then create a script
> > (cursor with sp_droplogin ) to delete them
> >
> > select sl.name
> > from master..syslogins sl
> > join sysusers su on sl.sid<>sl.sid
> >
> This doesn't make sense
> John
>|||Hi
"moharil" wrote:
> i have 2 sybase sp' s that run using the below listed SP i need something
> similar that runs on sql server 2000-05
> ******************sp_drop_login_completely ****************
> CREATE PROC sp_drop_login_completely @.login varchar(30)
> as
> declare @.msg varchar(20),
> @.cnt int,
> @.ret_code int,
> @.db varchar(30),
> @.status smallint,
> @.proc_name varchar(92),
> @.grp_nm varchar(30),
> @.aliased_user_nm varchar(30)
> select @.status = 0
> select @.ret_code = 0
> create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> aliased_user_nm varchar(30) null)
> /*
> ** Delete user/alias from databases for this login
> */
> declare databases_crs cursor for
> select name, status=status & 1024 from master..sysdatabases
> where
> status & 1024 != 1024 /* not read only */
> and status & 256 != 256 /* not suspect */
> and status & 44 != 44 /* not in for load status */
> for read only
> open databases_crs
> fetch databases_crs into @.db, @.status
> WHILE (@.@.sqlstatus = 0)
> BEGIN
> select @.grp_nm = null, @.aliased_user_nm = null
> select @.proc_name = @.db + "..sp_phh_model_group_nm"
> exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> output
> IF @.grp_nm is not null
> BEGIN
> insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> select @.proc_name = @.db + "..sp_dropuser"
> exec @.ret_code = @.proc_name @.login
> if @.ret_code != 0
> print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> @.login, @.db
> END
> IF @.aliased_user_nm is not null
> BEGIN
> insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> select @.proc_name = @.db + "..sp_dropalias"
> exec @.ret_code = @.proc_name @.login,"force"
> if (@.ret_code != 0)
> print 'Error: Unable to drop Sybase alias %1!, on database %2!
> ', @.login, @.db
> END
> select @.status = 0
> fetch databases_crs into @.db, @.status
> select @.status = @.status
> END
> close databases_crs
> deallocate cursor databases_crs
> /*
> ** Now that @.login isn't in any databases, drop the login
> */
> if suser_id(@.login) is not null
> BEGIN
> exec sp_droplogin @.login
> if (@.@.error != 0)
> print 'Error: Unable to drop Sybase login %1!', @.login
> END
> /*
> ** Show where the user was
> */
> print 'Login: %1! was removed from the following databases', @.login
> select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> isnull(aliased_user_nm,'') as aliased_user_nm from #display
> return
> go
> *************sp_phh_model_group_nm*******************
> create proc sp_model_group_nm
> @.model_to_follow varchar(30),
> @.grp_nm_model_is_in varchar(30) output,
> @.aliased_user_nm varchar(30) output
> as
> select @.grp_nm_model_is_in = g.name
> from sysusers u, sysusers g,
> master.dbo.syslogins m
> where u.suid *= m.suid
> and u.gid *= g.uid
> and u.name = @.model_to_follow
> and u.uid <= 16383 and u.uid != 0
> select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid => b.suid)
> from sysalternates a
> where suser_name(a.suid) = @.model_to_follow
> return
> go
> *******************************
> --
> Thanks & Regards
> Sid
Look at using the DATABASE_PROPERTY function instead of checking the status,
and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
sysusers to determine whether the user is a role or not.
There is an assumption that the users associated to the login do not own any
objects, check sysobjects and information_schema.schemata would be able to
deterine these.
John|||Hi John
> This doesn't make sense
What did you mean?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
> Uri
> "Uri Dimant" wrote:
>> Hi
>> Run this query to identify ophaned logins and then create a script
>> (cursor with sp_droplogin ) to delete them
>> select sl.name
>> from master..syslogins sl
>> join sysusers su on sl.sid<>sl.sid
> This doesn't make sense
> John
>|||Hi Uri
The most obvious problem would be comparing sl.sid <> sl.sid ! But even if
you change to su.sid there will be logins that are associated with other
users but not the current one and a login may be associated with users in
another database and not the current one! If you are looking for orphaned
users than you would take the sid from sysusers and make sure it wasn't in
syslogins which is the opposite way around to what you have (and would
require an outer join on the sids being the same), but I don't think that is
what the OP wants!
John
"Uri Dimant" wrote:
> Hi John
> > This doesn't make sense
>
> What did you mean?
>
>
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
> >
> > Uri
> >
> > "Uri Dimant" wrote:
> >
> >> Hi
> >> Run this query to identify ophaned logins and then create a script
> >> (cursor with sp_droplogin ) to delete them
> >>
> >> select sl.name
> >> from master..syslogins sl
> >> join sysusers su on sl.sid<>sl.sid
> >>
> >
> > This doesn't make sense
> >
> > John
> >
>
>|||Hi
"moharil" wrote:
> can we modify the listed sybase SP's to run on sql servers ?
> --
> Thanks & Regards
> Sid
>
I gave a list of changes in my other reply, you would need to work through
the code and test it against both SQL 2000 and SQL 2005.
John|||Hi John
Yep , I was mistaken , it means sl.sid <> su.sid. As you know when yopu
create a new login (without specifying SID) ,sql server generates a new
(randomaly) SID. So you are saying that if I restore database (SQL
Authentication) which has a user mapped to the login on the 'new' server
it could be that user's SID(of restored db) will be match to 'some' login in
the'new' server , do I understand you properly?
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:FE5E5E01-2BC0-4B51-88EF-2859E52B58D4@.microsoft.com...
> Hi Uri
> The most obvious problem would be comparing sl.sid <> sl.sid ! But even if
> you change to su.sid there will be logins that are associated with other
> users but not the current one and a login may be associated with users in
> another database and not the current one! If you are looking for orphaned
> users than you would take the sid from sysusers and make sure it wasn't in
> syslogins which is the opposite way around to what you have (and would
> require an outer join on the sids being the same), but I don't think that
> is
> what the OP wants!
> John
>
> "Uri Dimant" wrote:
>> Hi John
>> > This doesn't make sense
>>
>> What did you mean?
>>
>>
>> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
>> news:BE169473-B166-4A4D-8582-29E90D6F735B@.microsoft.com...
>> >
>> > Uri
>> >
>> > "Uri Dimant" wrote:
>> >
>> >> Hi
>> >> Run this query to identify ophaned logins and then create a script
>> >> (cursor with sp_droplogin ) to delete them
>> >>
>> >> select sl.name
>> >> from master..syslogins sl
>> >> join sysusers su on sl.sid<>sl.sid
>> >>
>> >
>> > This doesn't make sense
>> >
>> > John
>> >
>>|||Hi Uri
"Uri Dimant" wrote:
> Hi John
> Yep , I was mistaken , it means sl.sid <> su.sid. As you know when yopu
> create a new login (without specifying SID) ,sql server generates a new
> (randomaly) SID. So you are saying that if I restore database (SQL
> Authentication) which has a user mapped to the login on the 'new' server
> it could be that user's SID(of restored db) will be match to 'some' login in
> the'new' server , do I understand you properly?
>
I am not sure how sids are created to say if there is some element of them
that will not allow them to match other sids generated on a different
instance/machine. Orphaned users can be retrieved using sp_change_users_login
'report' so there is no real need to write your own code for finding them.
They can then be mapped by calling sp_change_users_login with either
update_one or auto_fix as the first parameter.
John|||Hi John
Its not always the way ,especially for non-experienced people to use
sp_change_users_login stored procedure which works very well as you pointed
So they prefer drop user or even login
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:5AEEE5A3-AF74-4D3D-9B67-E5C0A44BDC94@.microsoft.com...
> Hi Uri
> "Uri Dimant" wrote:
>> Hi John
>> Yep , I was mistaken , it means sl.sid <> su.sid. As you know when yopu
>> create a new login (without specifying SID) ,sql server generates a new
>> (randomaly) SID. So you are saying that if I restore database (SQL
>> Authentication) which has a user mapped to the login on the 'new' server
>> it could be that user's SID(of restored db) will be match to 'some' login
>> in
>> the'new' server , do I understand you properly?
> I am not sure how sids are created to say if there is some element of them
> that will not allow them to match other sids generated on a different
> instance/machine. Orphaned users can be retrieved using
> sp_change_users_login
> 'report' so there is no real need to write your own code for finding them.
> They can then be mapped by calling sp_change_users_login with either
> update_one or auto_fix as the first parameter.
> John|||Hi Uri
"Uri Dimant" wrote:
> Hi John
> Its not always the way ,especially for non-experienced people to use
> sp_change_users_login stored procedure which works very well as you pointed
> So they prefer drop user or even login
But then they have an issue with re-granting permission which if the used
sp_change_users_login does not require! I tend to find that the main reason
that people have for not using it they don't know about it, rather than ease
of use!
John|||i was able to modify the SP and i am able to run the SP the logins / user
gets deleted from the sql server . currenly i am working on writing a .sh
script on windows that will call the SP and send mails to our group saying
the login has been dropped
--
Thanks
SM
"John Bell" wrote:
> Hi
> "moharil" wrote:
> > i have 2 sybase sp' s that run using the below listed SP i need something
> > similar that runs on sql server 2000-05
> > ******************sp_drop_login_completely ****************
> > CREATE PROC sp_drop_login_completely @.login varchar(30)
> > as
> > declare @.msg varchar(20),
> > @.cnt int,
> > @.ret_code int,
> > @.db varchar(30),
> > @.status smallint,
> > @.proc_name varchar(92),
> > @.grp_nm varchar(30),
> > @.aliased_user_nm varchar(30)
> > select @.status = 0
> > select @.ret_code = 0
> > create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> > aliased_user_nm varchar(30) null)
> > /*
> > ** Delete user/alias from databases for this login
> > */
> > declare databases_crs cursor for
> > select name, status=status & 1024 from master..sysdatabases
> > where
> > status & 1024 != 1024 /* not read only */
> > and status & 256 != 256 /* not suspect */
> > and status & 44 != 44 /* not in for load status */
> > for read only
> > open databases_crs
> > fetch databases_crs into @.db, @.status
> > WHILE (@.@.sqlstatus = 0)
> > BEGIN
> > select @.grp_nm = null, @.aliased_user_nm = null
> > select @.proc_name = @.db + "..sp_phh_model_group_nm"
> > exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> > output
> > IF @.grp_nm is not null
> > BEGIN
> > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > select @.proc_name = @.db + "..sp_dropuser"
> > exec @.ret_code = @.proc_name @.login
> > if @.ret_code != 0
> > print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> > @.login, @.db
> > END
> > IF @.aliased_user_nm is not null
> > BEGIN
> > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > select @.proc_name = @.db + "..sp_dropalias"
> > exec @.ret_code = @.proc_name @.login,"force"
> > if (@.ret_code != 0)
> > print 'Error: Unable to drop Sybase alias %1!, on database %2!
> > ', @.login, @.db
> > END
> > select @.status = 0
> > fetch databases_crs into @.db, @.status
> > select @.status = @.status
> > END
> > close databases_crs
> > deallocate cursor databases_crs
> > /*
> > ** Now that @.login isn't in any databases, drop the login
> > */
> > if suser_id(@.login) is not null
> > BEGIN
> > exec sp_droplogin @.login
> > if (@.@.error != 0)
> > print 'Error: Unable to drop Sybase login %1!', @.login
> > END
> > /*
> > ** Show where the user was
> > */
> > print 'Login: %1! was removed from the following databases', @.login
> > select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> > isnull(aliased_user_nm,'') as aliased_user_nm from #display
> > return
> > go
> > *************sp_phh_model_group_nm*******************
> > create proc sp_model_group_nm
> > @.model_to_follow varchar(30),
> > @.grp_nm_model_is_in varchar(30) output,
> > @.aliased_user_nm varchar(30) output
> > as
> >
> > select @.grp_nm_model_is_in = g.name
> > from sysusers u, sysusers g,
> > master.dbo.syslogins m
> > where u.suid *= m.suid
> > and u.gid *= g.uid
> > and u.name = @.model_to_follow
> > and u.uid <= 16383 and u.uid != 0
> >
> > select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid => > b.suid)
> > from sysalternates a
> > where suser_name(a.suid) = @.model_to_follow
> >
> > return
> > go
> > *******************************
> >
> > --
> > Thanks & Regards
> > Sid
> Look at using the DATABASE_PROPERTY function instead of checking the status,
> and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
> SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
> sysusers to determine whether the user is a role or not.
> There is an assumption that the users associated to the login do not own any
> objects, check sysobjects and information_schema.schemata would be able to
> deterine these.
> John|||Hi
That is great!!
John
"moharil" wrote:
> i was able to modify the SP and i am able to run the SP the logins / user
> gets deleted from the sql server . currenly i am working on writing a .sh
> script on windows that will call the SP and send mails to our group saying
> the login has been dropped
> --
> Thanks
> SM
>
> "John Bell" wrote:
> > Hi
> >
> > "moharil" wrote:
> >
> > > i have 2 sybase sp' s that run using the below listed SP i need something
> > > similar that runs on sql server 2000-05
> > > ******************sp_drop_login_completely ****************
> > > CREATE PROC sp_drop_login_completely @.login varchar(30)
> > > as
> > > declare @.msg varchar(20),
> > > @.cnt int,
> > > @.ret_code int,
> > > @.db varchar(30),
> > > @.status smallint,
> > > @.proc_name varchar(92),
> > > @.grp_nm varchar(30),
> > > @.aliased_user_nm varchar(30)
> > > select @.status = 0
> > > select @.ret_code = 0
> > > create table #display (db_nm varchar(30), grp_nm varchar(30) null,
> > > aliased_user_nm varchar(30) null)
> > > /*
> > > ** Delete user/alias from databases for this login
> > > */
> > > declare databases_crs cursor for
> > > select name, status=status & 1024 from master..sysdatabases
> > > where
> > > status & 1024 != 1024 /* not read only */
> > > and status & 256 != 256 /* not suspect */
> > > and status & 44 != 44 /* not in for load status */
> > > for read only
> > > open databases_crs
> > > fetch databases_crs into @.db, @.status
> > > WHILE (@.@.sqlstatus = 0)
> > > BEGIN
> > > select @.grp_nm = null, @.aliased_user_nm = null
> > > select @.proc_name = @.db + "..sp_phh_model_group_nm"
> > > exec @.ret_code = @.proc_name @.login, @.grp_nm output, @.aliased_user_nm
> > > output
> > > IF @.grp_nm is not null
> > > BEGIN
> > > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > > select @.proc_name = @.db + "..sp_dropuser"
> > > exec @.ret_code = @.proc_name @.login
> > > if @.ret_code != 0
> > > print 'Error: Unable to drop Sybase user %1!, on database %2! ',
> > > @.login, @.db
> > > END
> > > IF @.aliased_user_nm is not null
> > > BEGIN
> > > insert #display values (@.db, @.grp_nm, @.aliased_user_nm)
> > > select @.proc_name = @.db + "..sp_dropalias"
> > > exec @.ret_code = @.proc_name @.login,"force"
> > > if (@.ret_code != 0)
> > > print 'Error: Unable to drop Sybase alias %1!, on database %2!
> > > ', @.login, @.db
> > > END
> > > select @.status = 0
> > > fetch databases_crs into @.db, @.status
> > > select @.status = @.status
> > > END
> > > close databases_crs
> > > deallocate cursor databases_crs
> > > /*
> > > ** Now that @.login isn't in any databases, drop the login
> > > */
> > > if suser_id(@.login) is not null
> > > BEGIN
> > > exec sp_droplogin @.login
> > > if (@.@.error != 0)
> > > print 'Error: Unable to drop Sybase login %1!', @.login
> > > END
> > > /*
> > > ** Show where the user was
> > > */
> > > print 'Login: %1! was removed from the following databases', @.login
> > > select db_nm, @.login as login_nm, isnull(grp_nm,'') as grp_nm,
> > > isnull(aliased_user_nm,'') as aliased_user_nm from #display
> > > return
> > > go
> > > *************sp_phh_model_group_nm*******************
> > > create proc sp_model_group_nm
> > > @.model_to_follow varchar(30),
> > > @.grp_nm_model_is_in varchar(30) output,
> > > @.aliased_user_nm varchar(30) output
> > > as
> > >
> > > select @.grp_nm_model_is_in = g.name
> > > from sysusers u, sysusers g,
> > > master.dbo.syslogins m
> > > where u.suid *= m.suid
> > > and u.gid *= g.uid
> > > and u.name = @.model_to_follow
> > > and u.uid <= 16383 and u.uid != 0
> > >
> > > select @.aliased_user_nm = (select b.name from sysusers b where a.altsuid => > > b.suid)
> > > from sysalternates a
> > > where suser_name(a.suid) = @.model_to_follow
> > >
> > > return
> > > go
> > > *******************************
> > >
> > > --
> > > Thanks & Regards
> > > Sid
> >
> > Look at using the DATABASE_PROPERTY function instead of checking the status,
> > and use LEFT JOIN instead of *= join syntax. sysalternates does not exist in
> > SQL Server 2000/2005 and you can use the columns isSQLRole, isAppRole etc in
> > sysusers to determine whether the user is a role or not.
> >
> > There is an assumption that the users associated to the login do not own any
> > objects, check sysobjects and information_schema.schemata would be able to
> > deterine these.
> >
> > John

drop login Stored Procedure

Drop login error

Hi
I am trying to drop a windows login via Management Studio (CTP June) but it comes up with
'Login domain\user' has granted one or more permissions. Revoke the permission before dropping the login (Microsoft SQL Server, Error: 15173)
I cannot see of any permissions that this user has granted. Is there a system view in SQL 2005 which shows permissions this user has granted?
Thanks,
Priyanga

You need to look at the "Security Catalog Views" topic in Books Online. Specifically, take a look at sys.server_permissions and sys.server_principals views to map principal_id of the login you are looking at to the permissions. You can do that at database level as well.

Something along these lines (use this as an idea):

select * from sys.server_permissions
where grantee_principal_id =
(select principal_id from sys.server_principals where name = N'DOMAIN\user')

Alternatively, in Object explorer, you can select Security->Logins->DOMAIN/user, Properties of the login, go Securables and add objects (e.g. server(s)) and this will show you explicit permissions of this login on the object(s). You can revoke there explicit permissions as well.

Then you can also go to the properties of the server in Object Explorer, Permissions and click on the login and click on Effective Permissions. This will show all the permissions for this user based on its role membership and explicitly granted permissions.

HTH,
Boris.

|||

Thanks Boris.

What was causing the issue was that the principal_id (nation\pk0159_a) was linked to grantor_principal_id in the sys.server_permissions as apposed to the grantee_principal_id. The object responsible for this issue was an endpoint. When i dropped the endpoint, i was allowed to drop nation\pk0159_a.

This explains why no objects were showing as being granted permissions by nation\pk0159_a in object explorer in SQL Management Studio.

Cheers,
Priyanga

Tuesday, March 27, 2012

Dropping Data Connect String

Thursday, March 22, 2012

Drop User

Drop User

Drop User

Monday, March 19, 2012

Drop role, user, login

drop login Stored Procedure

drop login Stored Procedure

drop login Stored Procedure

Drop login error

DSO in SQL2005

Blog Archive

About Me