Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
Message posted via http://www.droptable.com
Hi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FCC7EC4933C0@.droptable.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.droptable.com

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
Message posted via http://www.droptable.comHi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via droptable.com" <forum@.nospam.droptable.com> wrote in
message news:4FCC7EC4933C0@.droptable.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.droptable.com

Query for server user rights, databases and roles.

I'm trying to write a query that will list the server login name, the
databases they have access too, and what roles they have. I can do this
through Enterprise Manager no problem. I simply go to the server, security,
logins, right click on a user and it gives me the databases they have access
to plus their roles. All I want to do is write this in a query in query
analyzer. Thanks in advance for the help.
--
Message posted via http://www.sqlmonster.comHi,
Execute the below system stored proc from query analyzer:-
sp_helplogins <Login_name>
Thanks
Hari
SQL Server MVP
"Frank Nadal via SQLMonster.com" <forum@.nospam.SQLMonster.com> wrote in
message news:4FCC7EC4933C0@.SQLMonster.com...
> I'm trying to write a query that will list the server login name, the
> databases they have access too, and what roles they have. I can do this
> through Enterprise Manager no problem. I simply go to the server,
> security,
> logins, right click on a user and it gives me the databases they have
> access
> to plus their roles. All I want to do is write this in a query in query
> analyzer. Thanks in advance for the help.
> --
> Message posted via http://www.sqlmonster.com

Query blues... Help!

I have a security app that is like this: a Facility has many Apps and those
Apps may have any combination Modules, Roles, or Privileges.
Problem Query: Put together a query(s) that will delete all records related
to a single Facility.
CREATE TABLE Applications
(Appl_ID int IDENTITY,
FacilityID char(4) NOT NULL,
ApplName char(200) NOT NULL,
CONSTRAINT PK_Applications PRIMARY KEY CLUSTERED (Appl_ID))
go
CREATE TABLE ModulesRolesPrivileges
(MRP_ID int IDENTITY,
MRP_Name char(150) NOT NULL,
MRP_Type int NOT NULL, -- 1=Role 2=Module 3=Privilege
CONSTRAINT PK_ModulesRolesPrivileges PRIMARY KEY CLUSTERED (MRP_ID))
go
CREATE TABLE ApplicationsModulesRolesPrivileges -- Intersection table
between Applications and ModulesRolesPrivileges
(AMRP_ID int IDENTITY,
Appl_ID int NOT NULL,
MRP_ID int NOT NULL,
CONSTRAINT PK_ApplicationsModulesRolesPrivileges PRIMARY KEY CLUSTERED
(AMRP_ID),
CONSTRAINT R_1 FOREIGN KEY (Appl_ID) REFERENCES Applications (Appl_ID),
CONSTRAINT R_2 FOREIGN KEY (MRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
go
CREATE TABLE ModulesRolesPrivilegesHierarchy
(MRPH_ID int IDENTITY,
Parent_AMRP_ID int NOT NULL,
Child_AMRP_ID int NOT NULL,
CONSTRAINT PK_ModulesRolesPrivilegesHierarchy PRIMARY KEY CLUSTERED (MRPH_ID),
CONSTRAINT R_3 FOREIGN KEY (Parent_AMRP_ID) REFERENCES
ModulesRolesPrivileges (MRP_ID),
CONSTRAINT R_4 FOREIGN KEY (Child_AMRP_ID) REFERENCES ModulesRolesPrivileges
(MRP_ID))
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Host
Financials') -- 1
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'PeopleSoft')
-- 2
INSERT Applications (FacilityID, ApplName) VALUES ('0902', 'Acct Recv')
-- 3
INSERT Applications (FacilityID, ApplName) VALUES ('0914', 'Acct Recv')
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Budget', 2)
-- 1
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('GL', 2)
-- 2
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 3
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Admin', 1)
-- 4
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Query', 3)
-- 5
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Update', 3)
-- 6
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 7
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Supervisor', 1)
-- 8
INSERT ModulesRolesPrivileges (MRP_Name, MRP_Type) VALUES ('Delete', 3)
-- 9
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 1) -- 1
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (1, 2) -- 2
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (2, 3) -- 3
INSERT ApplicationsModulesRolesPrivileges (Appl_ID, MRP_ID) VALUES (4, 8) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (1, 3) -- 1
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 5) -- 2
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 6) -- 3
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (3, 7) -- 4
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 4) -- 5
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (4, 5) -- 6
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (2, 3) -- 7
INSERT ModulesRolesPrivilegesHierarchy (Parent_AMRP_ID, Child_AMRP_ID)
VALUES (8, 9) -- 8
Is Stored Procedure out of the question? If not, you can do the following.
1) SP will have one argument for FacilityID.
2)
Delete *
From ModulesRolesPrivilegesHierarchy
Where Parent_AMRP_ID IN (SELECT AMRP_ID
FROM ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID))
3)
Delete *
From ApplicationsModulesRolesPrivileges
WHERE Appl_ID IN (SELECT Appl_ID
FROM Applications
WHERE FacilityID = @.FacilityID)
4)
Delete *
From Applications
WHERE FacilityID = @.FacilityID
That should do the trick.
Hope that helps.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
|||Please ignore my answer, I misread table statements. So my answer is
incorrect.
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"Mohit K. Gupta" wrote:

> Is Stored Procedure out of the question? If not, you can do the following.
> 1) SP will have one argument for FacilityID.
> 2)
> Delete *
> From ModulesRolesPrivilegesHierarchy
> Where Parent_AMRP_ID IN (SELECT AMRP_ID
> FROM ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID))
> 3)
> Delete *
> From ApplicationsModulesRolesPrivileges
> WHERE Appl_ID IN (SELECT Appl_ID
> FROM Applications
> WHERE FacilityID = @.FacilityID)
> 4)
> Delete *
> From Applications
> WHERE FacilityID = @.FacilityID
> That should do the trick.
> Hope that helps.
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005